using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.IO;
using System.Xml;
using System.Diagnostics;
using System.Threading;
using System.Globalization;

/*=====================================================================

  File:      ProductsDB.cs for Adventure Works Cycles Storefront Sample
  Summary:   Middle tier component for browsing saleable items.
  Date:	     June 16, 2003

---------------------------------------------------------------------

  This file is part of the Microsoft SQL Server Code Samples.
  Copyright (C) Microsoft Corporation.  All rights reserved.

This source code is intended only as a supplement to Microsoft
Development Tools and/or on-line documentation.  See these other
materials for detailed information regarding Microsoft code samples.

THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
PARTICULAR PURPOSE.

======================================================= */

namespace Microsoft.Samples.SqlServer 
{

    //*******************************************************
    //
    // ProductDetails Class
    //
    // A simple data class that encapsulates details about
    // a particular product inside the AdventureWorks
    // database.
    //
	// Note that it is bad style to expose public fields.  So 
	// instead we maintain private fields with corresponding
	// public properties.  This helps avoid binary 
	// incompatabilities if the public fields were to have to
	// be changed to properties later.
	//
	//*******************************************************

    public class ProductDetails 
	{
        private String  modelNumber;
        private String  modelName;
        private String  productImage;
        private String unitCost;
        private String  description;

		// The fields above are exposed publically as properties
		// There is nothing fancy going on here, it is just better 
		// practice to not expose public fields.

		public String ModelNumber
		{
			get
			{
				return modelNumber;
			}
			set
			{
				modelNumber = value;
			}
		}

		public String ModelName
		{
			get
			{
				return modelName;
			}
			set
			{
				modelName = value;
			}
		}

		public String ProductImage
		{
			get
			{
				return productImage;
			}
			set
			{
				productImage = value;
			}
		}

		public String UnitCost
		{
			get
			{
				return unitCost;
			}
			set
			{
				unitCost = value;
			}
		}


		public String Description
		{
			get
			{
				return description;
			}
			set
			{
				description = value;
			}
		}

    }

    //*******************************************************
    //
    // ProductsDB Class
    //
    // Business/Data Logic Class that encapsulates all data
    // logic necessary to query products within
    // the AdventureWorks database.
    //
    //*******************************************************

    public class ProductsDB {

        //*******************************************************
        //
        // ProductsDB.GetProductTree() Method <a name="GetProductTree"></a>
        //
        // The GetProductCategories method returns a string that exposes all 
        // product categories and subcategories (and their CategoryIDs) within the Adventure Works Cycles   
        // database.  The string contains this information formatted as XML, which
		// is used in the menu tree control.
        //
        // Other relevant sources:
        //     + <a href="usp_ProductCategoryList.htm" style="color:green">usp_ProductCategoryList Stored Procedure</a>
        //
        //*******************************************************

		public DataSet GetProductTree() 
		{

			// Create Instance of Connection and Command Object
			SqlConnection myConnection = null;
			try
			{

				myConnection = new SqlConnection(Properties.Settings.Default.ConnectionString);
				SqlCommand myCommand = new SqlCommand("usp_ProductCategoryList", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Execute the command
				myConnection.Open();
				DataSet productsDs = new DataSet("Products");
				productsDs.Locale = CultureInfo.InvariantCulture;
				SqlDataAdapter productsDa = new SqlDataAdapter(myCommand);
				productsDa.Fill(productsDs, "ProductCategory");
				productsDs.Tables[1].TableName = "ProductSubcategory";
				//TODO: Switch sproc column name, then fix up relation column
				DataRelation categoryRel = new DataRelation("CategorySubCategories", productsDs.Tables["ProductCategory"].Columns["ProductCategoryID"],
					productsDs.Tables["ProductSubcategory"].Columns["ProductCategoryID"]);
				productsDs.Relations.Add(categoryRel);
				return productsDs;
			}
			finally
			{
				if (myConnection != null) myConnection.Close();
			}
		}

        //*******************************************************
        //
        // ProductsDB.GetProducts() Method <a name="GetProducts"></a>
        //
        // The GetProducts method returns a DataSet which contains all products within a specified
        // product category.  
        //
        // Other relevant sources:
        //     + <a href="usp_ProductsBySubcategory.htm" style="color:green">usp_ProductsBySubcategory Stored Procedure</a>
        //
        //*******************************************************

        public SqlDataReader GetProducts(int subCategoryID) {

            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand myCommand = myConnection.CreateCommand();
			myCommand.CommandText = "usp_ProductsBySubcategory";

            // Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure;

            // Add Parameters to SPROC
            SqlParameter parameterSubcategoryID = new SqlParameter("@SubcategoryID", SqlDbType.Int, 4);
            parameterSubcategoryID.Value = subCategoryID;
            myCommand.Parameters.Add(parameterSubcategoryID);

			SqlParameter parameterCurrencyCode = new SqlParameter("@Culture", SqlDbType.NVarChar, 10);
			parameterCurrencyCode.Value = CultureInfo.CurrentUICulture.Name;
			myCommand.Parameters.Add(parameterCurrencyCode);

            
            // Execute the stored procedure and Return the datareader result
			myConnection.Open();
            return myCommand.ExecuteReader(CommandBehavior.CloseConnection);
        }


        //*******************************************************
        //
        // ProductsDB.GetProductDetails() Method <a name="GetProductDetails"></a>
        //
        // The GetProductDetails method returns a ProductDetails
        // struct containing specific details about a specified
        // product within the AdventureWorks Database.
        //
        // Other relevant sources:
        //     + <a href="usp_ProductDetail.htm" style="color:green">usp_ProductDetail Stored Procedure</a>
        //
        //*******************************************************

        public ProductDetails GetProductDetails(int productID, String language) {

            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand myCommand = new SqlCommand("usp_ProductDetail", myConnection);

            // Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure;

            // Add Parameters to SPROC
            SqlParameter parameterProductID = new SqlParameter("@ProductID", SqlDbType.Int, 4);
            parameterProductID.Value = productID;
            myCommand.Parameters.Add(parameterProductID);

			SqlParameter parameterLanguage = new SqlParameter("@Language", SqlDbType.NChar, 2);
			parameterLanguage.Value = language.ToLower();
			myCommand.Parameters.Add(parameterLanguage);

			SqlParameter parameterCurrencyCode = new SqlParameter("@Culture", SqlDbType.NVarChar, 10);

			parameterCurrencyCode.Value = CultureInfo.CurrentUICulture.Name;
			myCommand.Parameters.Add(parameterCurrencyCode);


            SqlParameter parameterModelNumber = new SqlParameter("@ModelNumber", SqlDbType.NVarChar, 25);
            parameterModelNumber.Direction = ParameterDirection.Output;
            myCommand.Parameters.Add(parameterModelNumber);

            SqlParameter parameterModelName = new SqlParameter("@ModelName", SqlDbType.NVarChar, 50);
            parameterModelName.Direction = ParameterDirection.Output;
            myCommand.Parameters.Add(parameterModelName);

            SqlParameter parameterProductImage = new SqlParameter("@ProductImage", SqlDbType.NVarChar, 50);
            parameterProductImage.Direction = ParameterDirection.Output;
            myCommand.Parameters.Add(parameterProductImage);

			SqlParameter parameterUnitCost = new SqlParameter("@UnitCost", SqlDbType.NVarChar, 20);
			parameterUnitCost.Direction = ParameterDirection.Output;
			myCommand.Parameters.Add(parameterUnitCost);


            SqlParameter parameterDescription = new SqlParameter("@Description", SqlDbType.NVarChar, 3800);
            parameterDescription.Direction = ParameterDirection.Output;
            myCommand.Parameters.Add(parameterDescription);

            // Open the connection and execute the Command
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			finally
			{
				if (myConnection.State != ConnectionState.Closed)
					myConnection.Close();
			}

			//Fall back to US English descriptions if non-US English is not available
			if (parameterDescription.Value.Equals(DBNull.Value))
			{
				if (language == "EN")
					parameterDescription.Value = "";
				else
					return GetProductDetails(productID, "EN");
			}

            // Create and Populate ProductDetails Struct using
            // Output Params from the SPROC
            ProductDetails myProductDetails = new ProductDetails();

            myProductDetails.ModelNumber = (string)parameterModelNumber.Value;
            myProductDetails.ModelName = (string)parameterModelName.Value;
			if (parameterProductImage.Value.Equals(DBNull.Value))
				myProductDetails.ProductImage = "unknown.gif";
			else
				myProductDetails.ProductImage = ((string)parameterProductImage.Value).Trim();
            myProductDetails.UnitCost = (String)parameterUnitCost.Value;
            myProductDetails.Description = ((string)parameterDescription.Value).Trim();

            return myProductDetails;
        }

        

        //*******************************************************
        //
        // ProductsDB.GetMostPopularProductsOfWeek() Method <a name="GetMostPopularProductsOfWeek"></a>
        //
        // The GetMostPopularProductsOfWeek method returns a struct containing a 
        // forward-only, read-only DataReader containing the most popular products 
        // of the week within the AdventureWorks database.  
        // The SQLDataReaderResult struct also returns the
        // SQL connection, which must be explicitly closed after the
        // data from the DataReader is bound into the controls.
        //
        // Other relevant sources:
        //     + <a href="usp_ProductsMostPopular.htm" style="color:green">usp_ProductsMostPopular Stored Procedure</a>
        //
        //*******************************************************

        public SqlDataReader GetMostPopularProductsOfWeek() {

            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand myCommand = new SqlCommand("usp_ProductsMostPopular", myConnection);

            // Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure;

            // Execute the command
            myConnection.Open();
            SqlDataReader result = myCommand.ExecuteReader(CommandBehavior.CloseConnection);

            // Return the datareader result
            return result;
        }

        //*******************************************************
        //
        // ProductsDB.SearchProductDescriptions() Method <a name="SearchProductDescriptions"></a>
        //
        // The SearchProductDescriptions method returns a data set containing
        // a list of all
        // products whose name and/or description contains the specified search
        // string. 
        //
        // Other relevant sources:
        //     + <a href="usp_ProductSearch.htm" style="color:green">usp_ProductSearch Stored Procedure</a>
        //
        //*******************************************************

        public DataSet SearchProductDescriptions(string searchText, string language) {

            // Create Instance of Connection and Command Object
            SqlConnection myConnection = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlDataAdapter myCommand = new SqlDataAdapter("usp_ProductSearch", myConnection);

            // Mark the Command as a SPROC
            myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;

            // Add Parameters to SPROC
            SqlParameter parameterSearch = new SqlParameter("@Search", SqlDbType.NVarChar, 255);
            parameterSearch.Value = searchText;
            myCommand.SelectCommand.Parameters.Add(parameterSearch);

			SqlParameter parameterLanguage = new SqlParameter("@Language", SqlDbType.NChar, 2);
			parameterLanguage.Value = language.ToLower();
			myCommand.SelectCommand.Parameters.Add(parameterLanguage);

			SqlParameter parameterCurrencyCode = new SqlParameter("@Culture", SqlDbType.NVarChar, 10);
			parameterCurrencyCode.Value = CultureInfo.CurrentUICulture.Name;
			myCommand.SelectCommand.Parameters.Add(parameterCurrencyCode);



            // Execute the command
            DataSet result = new DataSet("SearchResults");
            result.Locale = CultureInfo.InvariantCulture;
            myCommand.Fill(result);		
			// If we get no hits, then try English
			if (result.Tables[0].Rows.Count == 0)
			{
				if (language.ToLower() != "en") return SearchProductDescriptions(searchText, "en");
			}
			
            // Return the DataSet result
            return result;
        }

    }
}